
import pandas as pd
import datetime as dt
import numpy as np

from pandas_profiling import ProfileReport
import cufflinks as cf
import plotly.express as px
from plotly.offline import init_notebook_mode

init_notebook_mode(connected= True)
cf.go_offline()


data = pd.read_csv('Datasets/kc_house_data.csv')


display(data.sample(5))


dimensionData = data.shape
print(f'This dataset has {dimensionData[0]} properties and {dimensionData[1]} available attributes.')

This dataset has 21613 properties and 21 available attributes.


list(data.columns)

['id',
 'date',
 'price',
 'bedrooms',
 'bathrooms',
 'sqft_living',
 'sqft_lot',
 'floors',
 'waterfront',
 'view',
 'condition',
 'grade',
 'sqft_above',
 'sqft_basement',
 'yr_built',
 'yr_renovated',
 'zipcode',
 'lat',
 'long',
 'sqft_living15',
 'sqft_lot15']


data.info()
#This dataset has no null values

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 21613 entries, 0 to 21612
Data columns (total 21 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   id             21613 non-null  int64  
 1   date           21613 non-null  object 
 2   price          21613 non-null  float64
 3   bedrooms       21613 non-null  int64  
 4   bathrooms      21613 non-null  float64
 5   sqft_living    21613 non-null  int64  
 6   sqft_lot       21613 non-null  int64  
 7   floors         21613 non-null  float64
 8   waterfront     21613 non-null  int64  
 9   view           21613 non-null  int64  
 10  condition      21613 non-null  int64  
 11  grade          21613 non-null  int64  
 12  sqft_above     21613 non-null  int64  
 13  sqft_basement  21613 non-null  int64  
 14  yr_built       21613 non-null  int64  
 15  yr_renovated   21613 non-null  int64  
 16  zipcode        21613 non-null  int64  
 17  lat            21613 non-null  float64
 18  long           21613 non-null  float64
 19  sqft_living15  21613 non-null  int64  
 20  sqft_lot15     21613 non-null  int64  
dtypes: float64(5), int64(15), object(1)
memory usage: 3.5+ MB


#Note 1: What is the relevance of some columns in this analysis?
#Columns to watch: 'view', 'grade', 'sqft_living15', 'sqft_lot15'
#Identify, transform or delete; 

#Note 2: Transform the attribute type 'date' and verify its compliance; 

#Note 3: It's necessary transforming 'condition' and 'waterview' in object type for response business questions


#view
data['view'].unique()

#Analyze: Scale how good the view is.
#Decision: Transform column type to object with classifications
# 0 and 1 - bad 
# 2 - regular
# 3 and 4 - good

array([0, 3, 4, 2, 1])


data['view'] = data['view'].apply(lambda x: 'bad' if x < 2 else 'regular' if x == 2 else 'good')
data['view'].unique()

array(['bad', 'good', 'regular'], dtype=object)


#grade
data['grade'].unique()

#It's an index from 1 to 13, where 1-3 falls short of building construction and design, 7 has an average level of construction and design, and 11-13 have a high quality level of construction and design.
#Defcision - Change the attribute 'grade' to low_quality_design, average_quality_design and high_quality_design

array([ 7,  6,  8, 11,  9,  5, 10, 12,  4,  3, 13,  1])


data['grade'] = data['grade'].apply (lambda x:   'high_quality_design' if x >= 11 else 
                                                'avg_quality_design' if (x > 3) & (x <= 10) else
                                                'low_quality_design')                     
data['grade'].unique()

array(['avg_quality_design', 'high_quality_design', 'low_quality_design'],
      dtype=object)


#Deleting unnecessary columns
#sqft_living15 - The square footage of interior housing living space for the nearest 15 neighbors; unnecessary
#sqft_lot15 - The square footage of the land lots of the nearest 15 neighbors; unnecessary

data = data.drop(['sqft_living15' , 'sqft_lot15'], axis= 1)


print(f'Now, this dataset has {data.shape[1]} attributes')

Now, this dataset has 19 attributes


#Checking Duplicates values
display(data[data['id'].duplicated(keep= False)])
display(data[data[['id', 'price']].duplicated(keep= False)])

#There are two types of duplicate data:
#os with same id but different prices(updated);
#and those with the same id and the same price;
#The most outdated types in relation to the date will be eliminated


#The rows with duplicate values that have the parameter 'keep' equal to 'first' are the most up-to-date data
#So these rows will remain.


#Deleting duplicates values
data = data.drop_duplicates(subset= ['id', 'price'], keep= 'first')
data = data.drop_duplicates(subset= ['id'], keep= 'first')

display(data[data[['id', 'price']].duplicated(keep= False)])
display(data[data['id'].duplicated(keep= False)])


#Reseting Index
data.reset_index(drop= True, inplace= True)


print(f'Now, this dataset has {data.shape[0]} registered properties')

Now, this dataset has 21436 registered properties


#Transforming date format for datetime
data['date'] = pd.to_datetime(data['date'], format= '%Y-%m-%d', errors= 'ignore')


#Separing and transforming date format to int
data['year'] = data['date'].dt.strftime('%Y')
data['year'] = data['year'].astype(int)

data['month'] = data['date'].dt.strftime('%m')
data['month'] = data['month'].astype(int)

data['days'] = data['date'].dt.strftime('%d')
data['days'] = data['days'].astype(int)


print(f'Now, this dataset has {data.shape[1]} attributes')

Now, this dataset has 22 attributes


data.columns.tolist()

['id',
 'date',
 'price',
 'bedrooms',
 'bathrooms',
 'sqft_living',
 'sqft_lot',
 'floors',
 'waterfront',
 'view',
 'condition',
 'grade',
 'sqft_above',
 'sqft_basement',
 'yr_built',
 'yr_renovated',
 'zipcode',
 'lat',
 'long',
 'year',
 'month',
 'days']


#Checking attribute compliance
describeDate = data[['year', 'month', 'days']].describe()
display(describeDate)
print('> YEAR')
print('The maximum year of property registration is {}'.format(data['year'].max()))
print('The minimum year of property registration is {}'.format(data['year'].min()))
print('> MONTH')
print('The maximum month of property registration is {}'.format(data['month'].max()))
print('The minimum month of property registration is {}'.format(data['month'].min()))
print('> DAYS')
print('The maximum days of property registration is {}'.format(data['days'].max()))
print('The minimum days of property registration is {}'.format(data['days'].min()))

> YEAR
The maximum year of property registration is 2015
The minimum year of property registration is 2014
> MONTH
The maximum month of property registration is 12
The minimum month of property registration is 1
> DAYS
The maximum days of property registration is 31
The minimum days of property registration is 1


#The date conforms to reality.


#Turning numerics atrribute into object attributes


#condition
# - If the value of the column "condition" is less than or equal to 2 > 'bad'
# - If the value of the column "condition" is equal to 3 or 4 > 'regular'
# - If the value of the column "condition" is equal to 5 > 'good'

data.loc[data['condition'] <= 2, 'condition'] = 'bad'
data.loc[(data['condition'] == 3) | (data['condition'] == 4), 'condition'] = 'regular'
data.loc[data['condition'] == 5, 'condition'] = 'good'
                                
display(data['condition'].unique())

array(['regular', 'good', 'bad'], dtype=object)


#waterfront
display(data['waterfront'].unique())
#0 - No 
#1- Yes
data.loc[data['waterfront'] == 0, 'waterfront'] = 'No'
data.loc[data['waterfront'] == 1, 'waterfront'] = 'Yes'

display(data['waterfront'].unique())

array([0, 1])

array(['No', 'Yes'], dtype=object)


data.dtypes

id                        int64
date             datetime64[ns]
price                   float64
bedrooms                  int64
bathrooms               float64
sqft_living               int64
sqft_lot                  int64
floors                  float64
waterfront               object
view                     object
condition                object
grade                    object
sqft_above                int64
sqft_basement             int64
yr_built                  int64
yr_renovated              int64
zipcode                   int64
lat                     float64
long                    float64
year                      int64
month                     int64
days                      int64
dtype: object


numericTypes = data.select_dtypes(include= 'number')
numericTypes = numericTypes.drop(['year' ,'month', 'days', 'lat', 'long'], axis= 1)


#Exploratory Analysis with pandas_profiling 
profile = ProfileReport(numericTypes)
profile.to_notebook_iframe()


#Analysis 
# - In bedrooms it is possible to have the presence of outliers (boxplot)
# - In sqft_lot is possible to have the presence of outliers (boxplot and max value)

#Note 3: It's necessary transforming 'condition' and 'waterview' in object type for response business questions


#Analyzing and transforming bedrooms outliers


px.box(data['bedrooms'], points= 'all')


q1, q3 = np.percentile(data['bedrooms'], [25, 75])
deq = q3 - q1
cii = q1 - (deq * 1.5) 
cis = q3 + (deq * 1.5)
cei = cii - (deq * 1.5) 
ces = cis + (deq * 1.5)

print(f'The upper inner fence is {cis} and lower inner fence is {cii}') # cis < x < cii - outliers
print(f'The upper outer fence is {ces} and lower outer fence is {cei}') # ces < x < cei - values more than discrepant

The upper inner fence is 5.5 and lower inner fence is 1.5
The upper outer fence is 7.0 and lower outer fence is 0.0


dfOutliersBedrooms = data.loc[(data['bedrooms'] > ces) | (data['bedrooms'] < cei)]
dfOutliersBedrooms.head()


dfOutliersBedrooms[['bedrooms', 'sqft_living']].groupby(['bedrooms']).count()
#In this dataset only will allow properties with up to 10 bedrooms.


#Property with 33 bedrooms
sqftLivingBedroom33 = data['sqft_living'].loc[data['bedrooms'] == 33].reset_index(drop= True)[0]

percent30 = sqftLivingBedroom33 * 0.3
dfSqftBed33Percent30 = data[['bedrooms', 'sqft_living']].loc[(data['sqft_living'] <= sqftLivingBedroom33 + percent30) & (data['sqft_living'] >= sqftLivingBedroom33 - percent30)]

print(dfSqftBed33Percent30.mean())
print(dfSqftBed33Percent30.median())

#Median number od bedrooms to house size() of the + or - 30% of 1620ft² equals 3!
#Soon line with 33 bedrooms will be changed to 3 bedrooms

bedrooms          3.143380
sqft_living    1619.285969
dtype: float64
bedrooms          3.0
sqft_living    1620.0
dtype: float64


medianBedrooms = dfSqftBed33Percent30['bedrooms'].median()
medianBedrooms

3.0


data.loc[data['bedrooms'] == 33, 'bedrooms'] = medianBedrooms


#Property with 11 bedrooms
sqftLivingBedroom11 = data['sqft_living'].loc[data['bedrooms'] == 11].reset_index(drop= True)[0]

percent30 = sqftLivingBedroom11 * 0.3
dfSqftBed11Percent30 = data[['bedrooms', 'sqft_living']].loc[(data['sqft_living'] <= sqftLivingBedroom11 + percent30) & (data['sqft_living'] >= sqftLivingBedroom11 - percent30)]

print(dfSqftBed11Percent30.mean())
print(dfSqftBed11Percent30.median())

bedrooms          3.857372
sqft_living    2718.732387
dtype: float64
bedrooms          4.0
sqft_living    2620.0
dtype: float64


medianBedrooms = dfSqftBed11Percent30['bedrooms'].median()
medianBedrooms

4.0


data.loc[data['bedrooms'] == 11, 'bedrooms'] = medianBedrooms


px.box(data['bedrooms'], points= 'all')


#Analyzing and tranforming sqft_lot outliers


px.box(data['sqft_lot'], points= 'all')


q1, q3 = np.percentile(data['sqft_lot'], [25, 75])
deq = q3 - q1
cii = q1 - (deq * 1.5) 
cis = q3  + (deq  * 1.5)
cei = cii  - (deq * 1.5) 
ces = cis + (deq * 1.5)
if cii < 0:
    cii = 0
if cei < 0:
    cei = 0

print(f'The upper inner fence is {cis} and lower inner fence is {cii}') # cis < x or x < cii - outliers
print(f'The upper outer fence is {ces} and lower outer fence is {cei}') # ces < x or x < cei - values more than discrepant

The upper inner fence is 19180.625 and lower inner fence is 0
The upper outer fence is 27665.0 and lower outer fence is 0


#There are many values that are outliers, but for business reasons, only values above 1.0M ft² will be considered outliers.


dfOutliersSqftLot = data.loc[data['sqft_lot']> 1000000].sort_values(['sqft_lot'], ascending= True)
dfOutliersSqftLot


meanPrice= dfOutliersSqftLot['price'].mean()
meanPrice

571875.0


percent10 = meanPrice * 0.1

dfPricePercent10 = data[['price', 'sqft_lot']].loc[(data['price'] > meanPrice - percent10) & (data['price'] < meanPrice + percent10)]
dfPricePercent10


meanDf = round(dfPricePercent10.mean(), 2)

print(meanDf)

price       566754.48
sqft_lot     16793.04
dtype: float64


data.loc[data['sqft_lot']> 1000000, 'sqft_lot'] = meanDf['sqft_lot']


px.box(data['sqft_lot'], points= 'all')


#Reseting index
data.reset_index(drop= True, inplace= True)
data


display(data.head())

dimensionData = data.shape
print(f'This dataset has {dimensionData[0]} properties and {dimensionData[1]} available attributes.')

This dataset has 21436 properties and 22 available attributes.


#First Solution


dfGroupZipcode = data[['price', 'zipcode']].groupby(['zipcode']).median()
dfGroupZipcode


data['status_median_price'] = 'NaN'
for i in range(len(dfGroupZipcode)):
    prices = data['price'].loc[data['zipcode'] == dfGroupZipcode.index[i]]
    for row in range(len(prices)):
        idx = prices.index[row]
        
        if prices.iloc[row] >= dfGroupZipcode['price'].iloc[i]:
            data['status_median_price'].iloc[idx] = 'above median'
        else:
            data['status_median_price'].iloc[idx] = 'bellow median'


data['buy'] = 'No'

data.loc[(data['waterfront'] == 'Yes') & (data['view'] != 'bad') & (data['condition'] != 'bad') & (data['status_median_price'] == 'above median'), 'buy'] = 'Yes'


#Second Solution


dfMedian = data[['price', 'zipcode']].groupby(['zipcode']).median().reset_index()
dfMedian.rename(columns= {'price':'median_price'}, inplace= True)
dfMedian.head()


data_buy = pd.merge(data, dfMedian, on= 'zipcode') 
data_buy.head()


for i in range(len(data_buy)):
    if (data_buy.loc[i, 'price'] < data_buy.loc[i, 'median_price']) & (data_buy.loc[i, 'condition'] != 'bad'):
        data_buy.loc[i, 'buy'] == 'Yes'
    else: 
        data_buy.loc[i, 'buy'] == 'No'

data_buy.head()


data_buy = data_buy[['id','date', 'zipcode', 'condition', 'price', 'median_price', 'buy']].loc[data_buy['buy'] == 'Yes'].reset_index(drop= True)
data_buy


#Saving Dataframe
data_buy.to_csv('Datasets/properties_to_be_purchased.csv')


#Summer: June(6) 21st to September(9) 23rd;
#Fall: September(9) 22 or 23 to December(12) 22;
#Winter: December(12) 22nd to March(3) 20th;
#Spring: March(3) 20th to June(6) 21st.


data['seasonality'] = 'NaN'

data.loc[(data['date'] >= '2014-06-21') & (data['date'] < '2014-09-23'), 'seasonality'] = 'Summer'

data.loc[((data['date'] >= '2014-09-23') & (data['date'] < '2014-12-22')), 'seasonality'] = 'Fall'

data.loc[(data['date'] >= '2014-12-22') & (data['date'] < '2015-03-20'), 'seasonality'] = 'Winter'

data.loc[(data['date'] < '2014-06-21') | (data['date'] >= '2015-03-20'), 'seasonality'] = 'Spring'


#The best time to sell real estate is in the spring, when properties prices are at their highest.


dfMedianZipcodeSeason = data[['price', 'zipcode', 'seasonality']].groupby(['zipcode', 'seasonality']).median().reset_index()
dfMedianZipcodeSeason.set_index('zipcode', inplace= True)
dfMedianZipcodeSeason


data['sale_price'] = 'NaN'
data['median_price'] = 'NaN'

zipcodes = list(data['zipcode'].unique())

for i in range(len(zipcodes)):
    priceAndSeason = data[['zipcode', 'price', 'seasonality']].loc[data['zipcode'] == zipcodes[i]]
                                                       
    for row in range(len(priceAndSeason)):
        idx = priceAndSeason.index[row]
        seasonality = priceAndSeason['seasonality'].iloc[row]
                                                        
        dfLoc = dfMedianZipcodeSeason.loc[(dfMedianZipcodeSeason.index == zipcodes[i]) &
                        (dfMedianZipcodeSeason['seasonality'] == seasonality)]
                                                        
        if priceAndSeason['price'].iloc[row] > dfLoc['price'].iloc[0]:
            data['sale_price'].iloc[idx] = data['price'].iloc[idx] * 1.1
            data['median_price'].iloc[idx] = dfLoc['price'].iloc[0]
        else:
            data['sale_price'].iloc[idx] = data['price'].iloc[idx] * 1.3
            data['median_price'].iloc[idx] = dfLoc['price'].iloc[0]


data_sale = data[['id', 'date', 'zipcode', 'seasonality', 'price', 'median_price', 'sale_price']]
data_sale


data_sale.rename(columns= {'price': 'buy_price'}, inplace= True)


data_sale['gain'] = 'Null'
for i in range(len(data_sale)):
    data_sale.loc[i, 'gain'] = data_sale.loc[i, 'sale_price'] - data_sale.loc[i, 'buy_price']


data_sale.head()


#Saving Dataframe 
data_sale.to_csv('Datasets/gain_from_sale_of_properties.csv')


#H1: Properties that have a good view of the water are 30% more expensive, on average, than those that don't;
noWaterfront = round(data['price'].loc[(data['waterfront'] == 'No')].mean(), 2)
print(f'The average price of properties that do not have a water view is US${noWaterfront}')

yesWaterfront = round(data['price'].loc[(data['waterfront'] == 'Yes') & (data['view'] != 'bad')].mean(), 2)
print(f'The average price of properties that overlook the water is US${yesWaterfront}')

rate = round((yesWaterfront - noWaterfront ) / noWaterfront * 100, 2)

print(f'Properties that have a good view of the water are {rate}% more expensive, on average, than those that don´t')

The average price of properties that do not have a water view is US$531937.2
The average price of properties that overlook the water is US$1668214.77
Properties that have a good view of the water are 213.61% more expensive, on average, than those that don´t


#H2: Buildings with a construction date before 1955 are 50% cheaper, on average.
after1995 = round(data['price'].loc[data['yr_built'] >= 1955].mean(), 2)
print(f'The average price for properties with years of construction after 1955 is US${after1995}')

prior1955 = round(data['price'].loc[data['yr_built'] < 1955].mean(), 2)
print(f'The average price for properties with years of contruction prior to 1955 is US${prior1955}')

rate1 = abs(round((prior1955 - after1995) / after1995 * 100, 2))

print(f'Properties with years of construction prior to 1955 are {rate1}% cheaper than the total average.')

The average price for properties with years of construction after 1955 is US$541478.25
The average price for properties with years of contruction prior to 1955 is US$538139.13
Properties with years of construction prior to 1955 are 0.62% cheaper than the total average.


#H3: Properties without a basement have sqft_lot 50% larger than those with a basement.
noBasement = round(data['sqft_lot'].loc[data['sqft_basement'] == 0].mean(), 2)
print(f'The land area for property that does have a basement is {noBasement}')
withBasement = round(data['sqft_lot'].loc[data['sqft_basement'] > 0].mean(), 2)
print(f'The land area for property that has a basement is {withBasement}')

rate2 = round((noBasement - withBasement) / withBasement * 100, 2)

print(f'Properties without a basement have a land area {rate2}% larger than those with a basement.')

The land area for property that does have a basement is 16034.74
The land area for property that has a basement is 13170.42
Properties without a basement have a land area 21.75% larger than those with a basement.


#H4: YoY ( Year over Year ) property price growth is 10%
year2014 = data['price'].loc[data['year'] == 2014].mean()
year2015 = data['price'].loc[data['year'] == 2015].mean()
print(year2014)
print(year2015)
growthYear = round((year2015 - year2014) / year2014 * 100, 2)
growthYear

print(f'There was a growth of {growthYear}%, that is, there was a decrease of {abs(growthYear)}% from 2014 to 2015.')

539321.490855538
543108.2865291794
There was a growth of 0.7%, that is, there was a decrease of 0.7% from 2014 to 2015.


#H5: Properties with 3 bathrooms have an average MoM (Month over Month) growth of 15%
property3Bathrooms = data[['price','date','month', 'year']].loc[data['bathrooms'] == 3]
property3Bathrooms = property3Bathrooms.sort_values(['date']).reset_index(drop= True)
##2014-05-02 > 2015-05-14 = 12 month

priceMinMonth = property3Bathrooms['price'].loc[(data['month'] == 5) & (data['year'] == 2014)].sum()
priceMaxMonth = property3Bathrooms['price'].loc[(data['month'] == 5) & (data['year'] == 2015)].sum()

growth_rate = (((priceMaxMonth / priceMinMonth) ** (1/12)) - 1) * 100

print(f'There was a {abs(round(growth_rate, 2))}% monthly decrease in the average price for properties with 3 bathrooms (from 2014-05 to 2015-05)')

There was a 9.66% monthly decrease in the average price for properties with 3 bathrooms (from 2014-05 to 2015-05)


#H6: Renovated properties are 15% or more expensive compared to the total average
dataMean = round(data['price'].mean(), 2)
print(f'The average dataset price is US${dataMean}')

dfRenovated = round(data['price'].loc[data['yr_renovated'] > 0].mean(), 2)
print(f'The average price of renovates property is US${dfRenovated}')

rate3 = round((dfRenovated - dataMean) / dataMean * 100, 2)

print(f'Renovated properties are {rate3}% more expensive compared to the total average.')

The average dataset price is US$540529.29
The average price of renovates property is US$761718.5
Renovated properties are 40.92% more expensive compared to the total average.


#H7: Properties with more than 3 bedrooms are 20% or more expensive compared to the total average
dataMean = round(data['price'].mean(), 2)
print(f'The average dataset price is US${dataMean}')

more3Bedrooms = round(data['price'].loc[data['bedrooms'] > 3].mean(), 2)
print(f'The average price for properties with more than 3 bedrooms is US${more3Bedrooms}')

rate4 = round((more3Bedrooms - dataMean) / dataMean * 100, 2)

print(f'Properties with more than 3 bedrooms are {rate4}% more expensive compared to the total average.')

The average dataset price is US$540529.29
The average price for properties with more than 3 bedrooms is US$671489.37
Properties with more than 3 bedrooms are 24.23% more expensive compared to the total average.


#H8: Properties in good condition correspond to 50% or more of the total property price.
priceTotal = round(data['price'].sum(), 2)
print(f'The total price of dataset is US${priceTotal}')

priceGoodCondition = round(data['price'].loc[data['condition'] == 'good'].sum(), 2)
print(f'The total price for properties with good condition is US${priceGoodCondition}')

rate5 = round((priceGoodCondition / priceTotal * 100), 2)

print(f'Properties in good condition correspond to {rate5}% of the total property price')

The total price of dataset is US$11586785800.0
The total price for properties with good condition is US$1031789320.0
Properties in good condition correspond to 8.9% of the total property price

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	view	...	grade	sqft_above	sqft_basement	yr_built	zipcode	lat	long	sqft_living15	sqft_lot15
14418	7215730120	20150504T000000	606500.0	3	2.50	2170	5500	2.0	0	...	8	2170	0	2000	98075	47.5975	-122.018	2170	5862
9764	7982600030	20140812T000000	219000.0	3	1.50	1200	12000	1.0	0	...	7	1200	0	1986	98001	47.2680	-122.245	1200	9405
1531	2864600105	20140624T000000	819000.0	3	3.50	2130	6150	2.0	2	...	8	1530	600	1908	98199	47.6491	-122.405	2040	5381
5629	3541600235	20141028T000000	350000.0	3	1.75	1900	10225	1.0	0	...	8	1220	680	1963	98166	47.4781	-122.357	1850	12630
4825	2741100280	20140513T000000	415000.0	3	1.75	1960	5000	1.0	0	...	6	980	980	1911	98108	47.5576	-122.317	1790	5000

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	waterfront	view	condition	grade	sqft_above	sqft_basement	yr_built	yr_renovated	zipcode	lat	long
93	6021501535	20140725T000000	430000.0	3	1.50	1580	5000	1.0	0	bad	3	avg_quality_design	1290	290	1939	0	98117	47.6870	-122.386
94	6021501535	20141223T000000	700000.0	3	1.50	1580	5000	1.0	0	bad	3	avg_quality_design	1290	290	1939	0	98117	47.6870	-122.386
313	4139480200	20140618T000000	1384000.0	4	3.25	4290	12103	1.0	0	good	3	high_quality_design	2690	1600	1997	0	98006	47.5503	-122.102
314	4139480200	20141209T000000	1400000.0	4	3.25	4290	12103	1.0	0	good	3	high_quality_design	2690	1600	1997	0	98006	47.5503	-122.102
324	7520000520	20140905T000000	232000.0	2	1.00	1240	12092	1.0	0	bad	3	avg_quality_design	960	280	1922	1984	98146	47.4957	-122.352
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
20670	8564860270	20150330T000000	502000.0	4	2.50	2680	5539	2.0	0	bad	3	avg_quality_design	2680	0	2013	0	98045	47.4759	-121.734
20779	6300000226	20140626T000000	240000.0	4	1.00	1200	2171	1.5	0	bad	3	avg_quality_design	1200	0	1933	0	98133	47.7076	-122.342
20780	6300000226	20150504T000000	380000.0	4	1.00	1200	2171	1.5	0	bad	3	avg_quality_design	1200	0	1933	0	98133	47.7076	-122.342
21580	7853420110	20141003T000000	594866.0	3	3.00	2780	6000	2.0	0	bad	3	avg_quality_design	2780	0	2013	0	98065	47.5184	-121.886
21581	7853420110	20150504T000000	625000.0	3	3.00	2780	6000	2.0	0	bad	3	avg_quality_design	2780	0	2013	0	98065	47.5184	-121.886

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	view	condition	grade	sqft_above	sqft_basement	yr_built	yr_renovated	zipcode	lat	long
3950	1825069031	20140814T000000	550000.0	4	1.75	2410	8447	2.0	good	4	avg_quality_design	2060	350	1936	1980	98074	47.6499	-122.088
3951	1825069031	20141016T000000	550000.0	4	1.75	2410	8447	2.0	good	4	avg_quality_design	2060	350	1936	1980	98074	47.6499	-122.088
14982	6308000010	20141208T000000	585000.0	3	2.50	2290	5089	2.0	bad	3	avg_quality_design	2290	0	2001	0	98006	47.5443	-122.172
14983	6308000010	20150423T000000	585000.0	3	2.50	2290	5089	2.0	bad	3	avg_quality_design	2290	0	2001	0	98006	47.5443	-122.172
20053	8648900110	20140505T000000	555000.0	3	2.50	1940	3211	2.0	bad	3	avg_quality_design	1940	0	2009	0	98027	47.5644	-122.093
20054	8648900110	20140826T000000	555000.0	3	2.50	1940	3211	2.0	bad	3	avg_quality_design	1940	0	2009	0	98027	47.5644	-122.093

	year	month	days
count	21436.000000	21436.000000	21436.000000
mean	2014.318949	6.590782	15.683476
std	0.466080	3.108176	8.636763
min	2014.000000	1.000000	1.000000
25%	2014.000000	4.000000	8.000000
50%	2014.000000	6.000000	16.000000
75%	2015.000000	9.000000	23.000000
max	2015.000000	12.000000	31.000000

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	waterfront	view	...	sqft_above	sqft_basement	yr_built	zipcode	lat	long	year	month	days
1645	9126101740	2014-12-04	490000.0	8	5.0	2800	2580	2.0	No	bad	...	1880	920	1997	98122	47.6086	-122.303	2014	12	4
4001	685000115	2014-10-07	2150000.0	8	6.0	4340	9415	2.0	No	bad	...	4340	0	1967	98004	47.6316	-122.202	2014	10	7
4033	7226500100	2015-02-19	373000.0	8	3.0	2850	12714	1.0	No	bad	...	2850	0	1959	98055	47.4859	-122.205	2015	2	19
4061	1997200215	2014-05-07	599999.0	9	4.5	3830	6988	2.5	No	bad	...	2450	1380	1938	98103	47.6927	-122.338	2014	5	7
4200	2902200015	2015-01-06	700000.0	9	3.0	3680	4400	2.0	No	bad	...	2830	850	1908	98102	47.6374	-122.324	2015	1	6

Projeto de Insights - King Count Houses

Imports ¶

Data Collect¶

Data Description¶

Data Cleaning and Data Tranformation¶

Note 1¶

Note 2¶

Note 3¶

Exploratory Data Analysis¶

Exploring Numerics Attributes¶

Treating Numerics Outliers¶

Solving Business Problems¶

What properties should House Rocket buy?¶

Once homes has been purchased, when is the best time to sell them and at what price?¶

Hypothesis Validation¶

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	waterfront	view	...	sqft_above	sqft_basement	yr_built	zipcode	lat	long	year	month	days
7711	2323089009	2015-01-19	855000.0	4	3.50	4030	1024068	2.0	No	bad	...	4030	0	2006	98045	47.4619	-121.744	2015	1	19
7589	2623069031	2014-05-21	542500.0	5	3.25	3010	1074218	1.5	No	bad	...	2010	1000	1931	98027	47.4564	-122.004	2014	5	21
17164	3326079016	2015-05-04	190000.0	2	1.00	710	1164794	1.0	No	bad	...	710	0	1915	98014	47.6888	-121.909	2015	5	4
1704	1020069017	2015-03-27	700000.0	4	1.00	1300	1651359	1.0	No	good	...	1300	0	1920	98022	47.2313	-122.023	2015	3	27

	price	sqft_lot
1	538000.0	7242
3	604000.0	5000
14	530000.0	4850
30	580500.0	3980
33	535000.0	3000
...	...	...
21404	594866.0	6000
21414	572000.0	3852
21418	520000.0	981
21421	541800.0	7866
21428	610685.0	6023

	id	date	price	bedrooms	bathrooms	sqft_living	sqft_lot	floors	waterfront	view	...	sqft_above	sqft_basement	yr_built	yr_renovated	zipcode	lat	long	year	month	days
0	7129300520	2014-10-13	221900.0	3	1.00	1180	5650.0	1.0	No	bad	...	1180	0	1955	0	98178	47.5112	-122.257	2014	10	13
1	6414100192	2014-12-09	538000.0	3	2.25	2570	7242.0	2.0	No	bad	...	2170	400	1951	1991	98125	47.7210	-122.319	2014	12	9
2	5631500400	2015-02-25	180000.0	2	1.00	770	10000.0	1.0	No	bad	...	770	0	1933	0	98028	47.7379	-122.233	2015	2	25
3	2487200875	2014-12-09	604000.0	4	3.00	1960	5000.0	1.0	No	bad	...	1050	910	1965	0	98136	47.5208	-122.393	2014	12	9
4	1954400510	2015-02-18	510000.0	3	2.00	1680	8080.0	1.0	No	bad	...	1680	0	1987	0	98074	47.6168	-122.045	2015	2	18
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
21431	263000018	2014-05-21	360000.0	3	2.50	1530	1131.0	3.0	No	bad	...	1530	0	2009	0	98103	47.6993	-122.346	2014	5	21
21432	6600060120	2015-02-23	400000.0	4	2.50	2310	5813.0	2.0	No	bad	...	2310	0	2014	0	98146	47.5107	-122.362	2015	2	23
21433	1523300141	2014-06-23	402101.0	2	0.75	1020	1350.0	2.0	No	bad	...	1020	0	2009	0	98144	47.5944	-122.299	2014	6	23
21434	291310100	2015-01-16	400000.0	3	2.50	1600	2388.0	2.0	No	bad	...	1600	0	2004	0	98027	47.5345	-122.069	2015	1	16
21435	1523300157	2014-10-15	325000.0	2	0.75	1020	1076.0	2.0	No	bad	...	1020	0	2008	0	98144	47.5941	-122.299	2014	10	15

	price
zipcode
98001	260000.0
98002	235000.0
98003	266500.0
98004	1150000.0
98005	765475.0
...	...
98177	557000.0
98178	279000.0
98188	265000.0
98198	265000.0
98199	687400.0

	id	date	zipcode	condition	price	median_price	buy
0	7567600045	2014-08-27	98178	regular	825000.0	279000.0	Yes
1	8069000075	2014-12-29	98178	regular	790000.0	279000.0	Yes
2	8073000550	2015-04-15	98178	regular	1700000.0	279000.0	Yes
3	8073000495	2014-10-10	98178	regular	700000.0	279000.0	Yes
4	623059016	2014-07-17	98178	regular	1100000.0	279000.0	Yes
...	...	...	...	...	...	...	...
146	6744701310	2015-04-15	98155	regular	1850000.0	374000.0	Yes
147	1568100920	2015-04-08	98155	regular	1950000.0	374000.0	Yes
148	4030100005	2014-12-09	98155	regular	1800000.0	374000.0	Yes
149	4030100290	2014-10-01	98155	regular	1680000.0	374000.0	Yes
150	2425049063	2014-09-11	98039	regular	3640900.0	1905000.0	Yes

	seasonality	price
zipcode
98001	Fall	250000.0
98001	Spring	265000.0
98001	Summer	262500.0
98001	Winter	260000.0
98002	Fall	236500.0
...	...	...
98198	Winter	275000.0
98199	Fall	650000.0
98199	Spring	730000.0
98199	Summer	722500.0
98199	Winter	597000.0

Projeto de Insights - King Count Houses

Imports¶

Data Collect¶

Data Description¶

Data Cleaning and Data Tranformation¶

Note 1¶

Note 2¶

Note 3¶

Exploratory Data Analysis¶

Exploring Numerics Attributes¶

Treating Numerics Outliers¶

Solving Business Problems¶

What properties should House Rocket buy?¶

Once homes has been purchased, when is the best time to sell them and at what price?¶

Hypothesis Validation¶

Imports ¶